import model.model as ut
from utils.common.db_connection import db


def search_results_with_status(work_order_id: int, view_status: int):
    """
    通过工单id查询相关结果
    :param view_status: 提交状态 0：all，1：已提交，2：未提交，3：提交失败
    :param work_order_id: 工单id
    :return:
    """
    if view_status == 0:
        return search_results(work_order_id)
    elif view_status == 1:
        return db.session.query(ut.Topic).filter_by(work_order_id=work_order_id, submitted=2).all()  # 对应数据库中的状态2
    elif view_status == 2:
        return db.session.query(ut.Topic).filter_by(work_order_id=work_order_id, submitted=0).all()  # 对应数据库中的状态0
    elif view_status == 3:
        return db.session.query(ut.Topic).filter_by(work_order_id=work_order_id, submitted=-1).all()  # 对应数据库中的状态-1


def search_results(work_order_id: int):
    """
    通过工单id查询相关结果
    :param work_order_id:
    :return:
    """
    items = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).all()
    return items


def insert_result(data: str, wo_id: int):
    """
    修改工单状态
    :param data: 提交的数据
    :param work_order_id: 工单id
    :return:
    """

    new_obj = ut.Topic(data=data, work_order_id=wo_id)
    db.session.add(new_obj)
    db.session.commit()


def modify_result(topic_id: int, cur_data: str):
    """
    通过topic_id修改数据
    :param topic_id: topic_id
    :param cur_data: 提交的数据
    :return:
    """

    item = ut.Topic.query.get(topic_id)
    if item:
        item.data = cur_data
        db.session.add(item)
        db.session.commit()
    return item


def delete_result_by_word_order_id(work_order_id: int):
    """
    通过work_order_id删除工单
    :param work_order_id:
    :return:
    """
    # 查询符合条件的数据并删除
    try:
        db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).delete()
        # data_to_delete = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).all()
        # for data in data_to_delete:
        #     db.session.delete(data)
        # db.session.delete(data_to_delete)
        db.session.commit()
    except Exception as e:
        raise Exception
    finally:
        db.session.close()


def batch_insert(topics: [ut.Topic]):
    # 批量提交

    db.session.add_all(topics)
    db.session.commit()


def search_by_id(topic_id: int) -> [ut.Topic]:
    """
    通过主键id查询
    :param topic_id:
    :return:
    """
    return ut.Topic.query.get(topic_id)


def search_by_work_order_id(work_order_id: int) -> [ut.Topic]:
    """
    通过work_order_id查询所有基础数据
    :param work_order_id:
    :return:
    """
    datas = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).all()
    return datas


def search_by_work_order_id_submitted_status(work_order_id: int, submitted: int) -> [ut.Topic]:
    """
    通过work_order_id查询所有基础数据
    :param work_order_id:
    :param submitted:
    :return:
    """
    datas = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).filter_by(submitted=submitted).all()
    return datas


def search_by_work_order_id_status(work_order_id: int) -> [ut.Topic]:
    """
    通过work_order_id查询所有基础数据
    :param work_order_id:
    :return:
    """
    datas = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).order_by(ut.Topic.submitted.desc()).all()
    return datas


def search_by_work_order_id_submitted(work_order_id: int) -> [ut.Topic]:
    """
    通过work_order_id查询所有基础数据
    :param work_order_id:
    :return:
    """
    datas = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).filter_by(submitted=2).count()
    return datas


def search_submitted_by_work_orders(work_order_ids: [int]) -> [ut.Topic]:
    """
    通过work_order_id查询所有基础数据
    :param work_order_ids:
    :return:
    """
    # datas = db.session.query(ut.Topic).filter_by(ut.Topic.work_order_id.in_(work_order_ids)).filter_by(
    #     submitted=2).group_by(ut.Topic.work_order_id)
    result = db.session.query(ut.Topic.work_order_id, db.func.count().label('count')). \
        filter(ut.Topic.work_order_id.in_(work_order_ids)). \
        filter(ut.Topic.submitted == 2). \
        group_by(ut.Topic.work_order_id). \
        all()
    return result


def search_by_work_order_id_submit_failed(work_order_id: int) -> [ut.Topic]:
    """
    通过work_order_id查询所有基础数据,查询提交失败的数据
    :param work_order_id:
    :return:
    """
    datas = db.session.query(ut.Topic).filter_by(work_order_id=work_order_id).filter_by(submitted=-1).count()
    return datas


def batch_update_by_id(topics: [ut.Topic]):
    """
    通过id批量更新数据
    :param topics:
    :return:
    """
    # db.session.bulk_update_mappings(ut.Topic, topics)

    for i in topics:
        db.session.add(i)
    # 提交更改
    db.session.commit()
